import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历及总体_对学校人才培养工作的建议(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历及总体，对学校人才培养工作的建议 - 指标计算函数
    
    ## 指标说明
    该指标用于统计各学历层次毕业生对学校人才培养工作的建议分布情况。
    计算逻辑为：统计每个选项被选择的人数占所有回答该问题人数的比例。
    最终结果包含各学历层次(本科、专科、硕士、博士)的分布情况以及总体分布情况。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": {
            "总体": {
                "强化专业基础知识教育": 0.35,
                "强化实践动手能力培养": 0.28,
                "强化创新能力培养": 0.22,
                "强化思想道德教育": 0.15,
                "强化心理健康教育": 0.12,
                "强化体育锻炼": 0.08,
                "强化美育熏陶": 0.05,
                "其他": 0.03
            },
            "本科毕业生": {
                "强化专业基础知识教育": 0.32,
                "强化实践动手能力培养": 0.30,
                "强化创新能力培养": 0.20,
                "...": "..."
            },
            "...": "..."
        }
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历及总体，对学校人才培养工作的建议, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year, dy_target_items, split_tb_paper 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        split_tb_paper = project_info['split_tb_paper']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}, split_tb_paper={split_tb_paper}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 查询问卷信息
        questionnaire_sql = f"""
        SELECT id, dy_target 
        FROM wt_template_customer 
        WHERE id IN ({','.join(['%s'] * len(questionnaire_ids))})
        """
        questionnaires = db.fetchall(questionnaire_sql, tuple(questionnaire_ids))
        if not questionnaires:
            raise ValueError(f"未找到问卷ID集合={questionnaire_ids}的配置信息")
        
        logger.info(f"查询到问卷信息: {questionnaires}")

        # 4. 过滤特定调研对象的问卷
        valid_questionnaire_ids = [q['id'] for q in questionnaires if q['dy_target'] == 'GRADUATE_SHORT']
        if not valid_questionnaire_ids:
            raise ValueError("未找到目标调研对象的问卷ID")
            
        logger.info(f"找到有效问卷ID: {valid_questionnaire_ids}")

        # 5. 查询问题信息
        question_sql = """
        SELECT id, wt_code, wt_obj 
        FROM wt_template_question_customer 
        WHERE cd_template_id = %s AND wt_code = %s AND is_del = 0
        """
        question_info = db.fetchone(question_sql, (valid_questionnaire_ids[0], 'T00003311'))
        if not question_info:
            raise ValueError("未找到指定问题编码的问题信息")
            
        logger.info(f"找到问题信息: {question_info['id']}")

        # 6. 解析问题选项
        wt_obj = json.loads(question_info['wt_obj'])
        options = [{
            'key': item['key'],
            'val': item['val'],
            'weight': item.get('weight', 1)
        } for item in wt_obj['itemList']]

        # 7. 构建动态表名
        answer_table = f"re_dy_paper_answer_{split_tb_paper}"
        student_table = f"dim_client_target_baseinfo_student_{item_year}"

        # 8. 定义学历列表
        education_levels = ['本科毕业生', '专科毕业生', '硕士研究生', '博士研究生']
        
        # 9. 计算各学历及总体结果
        result = {}
        
        # 9.1 计算总体结果
        sql = f"""
        SELECT
            CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c1)/COUNT(*) END as '强化专业基础知识教育',
            CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c2)/COUNT(*) END as '强化实践动手能力培养',
            CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c3)/COUNT(*) END as '强化创新能力培养',
            CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c4)/COUNT(*) END as '强化思想道德教育',
            CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c5)/COUNT(*) END as '强化心理健康教育',
            CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c6)/COUNT(*) END as '强化体育锻炼',
            CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c7)/COUNT(*) END as '强化美育熏陶',
            CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c8)/COUNT(*) END as '其他',
            COUNT(*) as total_count
        FROM {answer_table} t1 
        JOIN {student_table} s ON t1.target_no = s.target_no
        WHERE
            t1.cd_template_id = %s
            AND t1.wid = %s
            AND t1.ans_true = 1
            AND s.shard_tb_key = %s
            AND s.item_year = %s
        """
        total_result = db.fetchone(sql, (valid_questionnaire_ids[0], question_info['id'], shard_tb_key, item_year))
        if total_result:
            total_count = total_result.pop('total_count', 0)
            logger.info(f"总体数据查询成功，记录数: {total_count}")
            result['总体'] = {k: float(v) if v is not None else 0.0 for k, v in total_result.items()}
        else:
            logger.warning("总体数据查询结果为空")
            result['总体'] = {}
        
        # 9.2 计算各学历层次结果
        for edu in education_levels:
            edu_sql = f"""
            SELECT
                CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c1)/COUNT(*) END as '强化专业基础知识教育',
                CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c2)/COUNT(*) END as '强化实践动手能力培养',
                CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c3)/COUNT(*) END as '强化创新能力培养',
                CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c4)/COUNT(*) END as '强化思想道德教育',
                CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c5)/COUNT(*) END as '强化心理健康教育',
                CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c6)/COUNT(*) END as '强化体育锻炼',
                CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c7)/COUNT(*) END as '强化美育熏陶',
                CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(c8)/COUNT(*) END as '其他',
                COUNT(*) as total_count
            FROM {answer_table} t1 
            JOIN {student_table} s ON t1.target_no = s.target_no
            WHERE
                t1.cd_template_id = %s
                AND t1.wid = %s
                AND t1.ans_true = 1
                AND s.shard_tb_key = %s
                AND s.item_year = %s
                AND s.education = %s
            """
            edu_result = db.fetchone(edu_sql, (valid_questionnaire_ids[0], question_info['id'], shard_tb_key, item_year, edu))
            if edu_result:
                edu_count = edu_result.pop('total_count', 0)
                logger.info(f"{edu}数据查询成功，记录数: {edu_count}")
                if edu_count > 0:
                    result[edu] = {k: float(v) if v is not None else 0.0 for k, v in edu_result.items()}
                else:
                    logger.warning(f"{edu}没有找到有效数据")
            else:
                logger.warning(f"{edu}数据查询结果为空")
        
        logger.info(f"指标 '各学历及总体，对学校人才培养工作的建议' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历及总体，对学校人才培养工作的建议' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历及总体，对学校人才培养工作的建议",
            "code": 500,
            "error": str(e)
        }